﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace tingtinig
{
    public partial class Form : System.Windows.Forms.Form
    {
        public Form()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.ShowDialog();
            // 文件地址
            string path = ofd.FileName;

            if (string.IsNullOrEmpty(path))
            {
                MessageBox.Show("请选择 Excel 文件！！");
                return;
            }

            // 获取文件后缀名称
            string fileExt = Path.GetExtension(path).ToLower();

            IWorkbook workbook;
            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                if (".xlsx" == fileExt)
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else if (".xls" == fileExt)
                {
                    workbook = new HSSFWorkbook(fs);
                }
                else
                {
                    MessageBox.Show("请选择 Excel 文件！！");
                    return;
                }
            }


            ISheet sheet = workbook.GetSheetAt(0);
            IRow row = sheet.GetRow(0);

            HashSet<string> colNames = new HashSet<string>();
            colNames.Add("产品分类");
            colNames.Add("产品/项目名称");
            colNames.Add("产品核算名称");
            colNames.Add("是否赠送");

            // 查找各列的位置
            Dictionary<string, int> colIndex = new Dictionary<string, int>();
            for (int i = 0; i < row.LastCellNum; i++)
            {
                if (row.GetCell(i) == null) break;

                string colName = row.GetCell(i).StringCellValue;
                if (colNames.Contains(colName))
                {
                    colIndex[colName] = i;
                }
            }

            // 创建结果 sheet
            ISheet newSheet = workbook.GetSheet("统计结果");
            if (newSheet == null)
            {
                newSheet = workbook.CreateSheet("统计结果");

            }

            // 设置产品核算名称所在行的位置
            Dictionary<string, int> rowDic = new Dictionary<string, int>();
            // 设置产品/项目名称所在列的位置
            Dictionary<string, int> colDic = new Dictionary<string, int>();
            // 计算免费数量
            Dictionary<string, int> freeDic = new Dictionary<string, int>();

            newSheet.CreateRow(0).CreateCell(0).SetCellValue("产品核算名称");
            newSheet.GetRow(0).CreateCell(1).SetCellValue("产品分类");

            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                string productCheckName = sheet.GetRow(i).GetCell(colIndex["产品核算名称"]).StringCellValue;
                // 为空跳转
                if (string.IsNullOrEmpty(productCheckName)) break;

                if (!rowDic.ContainsKey(productCheckName))
                {
                    rowDic.Add(productCheckName, rowDic.Count + 1);
                    string productTypeName = sheet.GetRow(i).GetCell(colIndex["产品分类"]).StringCellValue;
                    newSheet.CreateRow(rowDic.Count).CreateCell(0).SetCellValue(productCheckName);
                    newSheet.GetRow(rowDic.Count).CreateCell(1).SetCellValue(productTypeName);
                }
                string productName = sheet.GetRow(i).GetCell(colIndex["产品/项目名称"]).StringCellValue;
                if (!colDic.ContainsKey(productName))
                {
                    colDic.Add(productName, colDic.Count + 2);
                    newSheet.GetRow(0).CreateCell(colDic.Count + 1).SetCellValue(productName);
                }

                //  数量统计
                ICell cell = newSheet.GetRow(rowDic[productCheckName]).GetCell(colDic[productName]);
                if (cell == null)
                {
                    cell = newSheet.GetRow(rowDic[productCheckName]).CreateCell(colDic[productName]);
                    cell.SetCellValue(0);
                }
                cell.SetCellValue(cell.NumericCellValue + 1);


                // 是否赠送
                ICell isFreeCell = sheet.GetRow(i).GetCell(colIndex["是否赠送"]);
                string isFree;
                if (isFreeCell != null && (isFree = isFreeCell.StringCellValue) == "是")
                {
                    if (freeDic.ContainsKey(productCheckName))
                    {
                        freeDic[productCheckName] = freeDic[productCheckName] + 1;
                    }
                    else
                    {
                        freeDic[productCheckName] = 1;
                    }
                }

            }
            int freeNum = colDic.Count + 2;
            newSheet.GetRow(0).CreateCell(freeNum).SetCellValue("免单数量");
            foreach (var item in freeDic)
            {
                newSheet.GetRow(rowDic[item.Key]).CreateCell(freeNum).SetCellValue(item.Value);
            }

            using (FileStream fs = File.Create("计算结果.xlsx"))
            {
                workbook.Write(fs);
                workbook.Close();
            }

            MessageBox.Show("完成");




        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }
    }
}
